Commerce Stream Practicals

SOP 2: Database using LibreOffice Base

Create table employee with fields names - empid, empname, empdept, empqual, empjoindate, empsal etc. Insert minimum 8 records and solve:

1. Where employee qualification is "MBA".
2. Where employee department is "Accounts".
3. Where employee salary > 70000.
4. Where employee name is "Mr. Suhas Kale".

Solution

i) Steps for creating a database

• Click on Start → All Program → LibreOffice → LibreOffice Base.
• Database wizard window appears, select Save and Proceed → Click on Finish button, select proper location where you wish to save the database with appropriate name, and database will be created.

ii) Steps for creating a table

• To create a table, click on Create table in design view → Table design window appears, in that window set fieldname and datatype as required.
• To assign PRIMARY KEY to empid field, right click on the small button present on the left side of the fieldname and select Primary key option.
• To save click on save button or press CTRL+S, give table name as employee and click OK; table will be saved.

iii) Steps for inserting records in a table

• For inserting records, double click on the table in which data is to be inserted; a window will appear on the screen; type data under each field and complete all the records.

iv) Steps for creating query

1. Click on Queries object and in right pane click on Create Query in Design View option.
2. From ADD table window select table and click on ADD button and click on Close.
3. Select all the fields one by one by clicking on the field's columns and solve the following queries.

a) To display records where employee qualification is "MBA"

• In Criteria under empqual type: MBA and click Run query icon or press F5.

b) To display records where employee department is "ACCOUNTS"

• In Criteria under empdept type: ACCOUNTS and click Run Query or press F5.

c) To display records where employee salary > 70000

• In Criteria under empsal type: >70000 (or enter 70000 depending on locale format) and click Run Query icon or press F5.

d) To display records where employee name is "Mr. Suhas Kale"

• In Criteria under empname type: Mr. Suhas Kale and click Run Query icon or press F5.

Notes

• Ensure primary key is set for empid and correct datatypes are chosen for date and numeric fields to prevent input/formatting issues.
• Save each query after successful run for future report generation and evaluation.